﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml;
using System.Reflection;

namespace Xiaov.SmartWeb.Application.SupCan
{
    public class ConvertXmlToSql<T> where T : new()
    {
        /// <summary>
        /// 增删改操作
        /// </summary>
        /// <param name="xmlData">xml字符串</param>
        /// <param name="table">表名</param>
        /// <param name="isAddObject">是否要进行添加操作</param>
        /// <param name="isUpdateObject">是否要进行修改操作</param>
        /// <param name="isDeleteObject">是否要进行删除操作</param>
        public static void AddUpdateDeleteObject(string xmlData, string table, bool isAddObject, bool isUpdateObject, bool isDeleteObject)
        {
            XmlDocument xmlInfo = new XmlDocument();
            xmlInfo.LoadXml(xmlData);
            if (isDeleteObject)
            {
                DeleteObject(xmlInfo, table);
            }
            if (isUpdateObject)
            {
                //修改 
                UpdatedObject(xmlInfo, table);
            }
            if (isAddObject)
            {
                AddObject(xmlInfo, table);
            }
        }

        /// <summary>
        /// 增删改操作
        /// </summary>
        /// <param name="xmlData">xml字符串</param>
        /// <param name="table">表名</param>
        public static void AddUpdateDeleteObject(string xmlData, string table)
        {
            AddUpdateDeleteObject(xmlData, table, true, true, true);
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="xmlInfo">XmlDocument</param>
        /// <param name="table">表名</param>
        public static void AddObject(XmlDocument xmlInfo, string table)
        {
            string sql = "begin ";
            string tempName = "";
            
            XmlElement xmlElement = xmlInfo["table"]["newRow"];
            if (xmlElement == null)
            {
                return;
            }
            T t = new T();
            PropertyInfo[] properties = t.GetType().GetProperties();
            foreach (XmlNode item in xmlElement.ChildNodes)
            {
                sql += "insert into " + table + "(";
                string sqlMain = "";
                string sqlColumn = "";
                foreach (PropertyInfo pi in properties)
                {
                    tempName = pi.Name;
                    if (item[tempName] != null)
                    {
                        sqlColumn += tempName + ",";
                        sqlMain += "'" + item[tempName].InnerText.Replace("\r\n", "") + "',";
                    }
                }
                sql += sqlColumn.Substring(0, sqlColumn.Length - 1) + ") values (";
                sql += sqlMain.Substring(0, sqlMain.Length - 1) + ");";
            }           
            sql += "null; ";
            sql += "end; ";
            Maticsoft.DBUtility.DbHelperOra.ExecuteSql(sql);                                    
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="xmlInfo">XmlDocument</param>
        /// <param name="table">表名</param>
        public static void UpdatedObject(XmlDocument xmlInfo, string table)
        {
            string tempName = "";
            XmlElement xmlElement = xmlInfo["table"]["modifiedRow"];
            if (xmlElement == null)
            {
                return;
            }
            T t = new T();
            PropertyInfo[] properties = t.GetType().GetProperties();
            string sql = "begin ";
            foreach (XmlNode item in xmlElement.ChildNodes)
            {
                sql += "update " + table + " set ";
                string sqlWhere = " where ";
                string[] key = xmlInfo["table"].Attributes["key"].Value.Split(',');
                string[] keyValue = item.Attributes["key"].Value.Split(',');
                for (int i = 0; i < key.Length; i++)
                {
                    if (i != 0)
                    {
                        sqlWhere += " and ";
                    }
                    sqlWhere += key[i] + "='" + keyValue[i] + "'";
                }
                foreach (PropertyInfo pi in properties)
                {
                    tempName = pi.Name;
                    if (item[tempName] != null)
                    {
                        sql += tempName + "='" + item[tempName].InnerText.Replace("\r\n", "") + "',";
                    }
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += sqlWhere;
                sql += ";";
            }
            sql += "null; ";
            sql += "end; ";
            Maticsoft.DBUtility.DbHelperOra.ExecuteSql(sql.ToString());
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="xmlInfo">XmlDocument</param>
        /// <param name="table">表名</param>
        public static void DeleteObject(XmlDocument xmlInfo, string table)
        {
            XmlElement xmlElement = xmlInfo["table"]["deletedRow"];
            if (xmlElement == null)
            {
                return;
            }
            string sqlDel = "begin ";
            foreach (XmlNode item in xmlElement.ChildNodes)
            {
                sqlDel += "delete from " + table;
                string sqlWhere = " where ";
                string[] key = xmlInfo["table"].Attributes["key"].Value.Split(',');
                string[] keyValue = item.Attributes["key"].Value.Split(',');
                for (int i = 0; i < key.Length; i++)
                {
                    if (i != 0)
                    {
                        sqlWhere += " and ";
                    }
                    sqlWhere += key[i] + "='" + keyValue[i] + "'";
                }
                sqlDel += sqlWhere;
                sqlDel += ";";
            }

            sqlDel += "null; ";
            sqlDel += "end; ";
            Maticsoft.DBUtility.DbHelperOra.ExecuteSql(sqlDel);
        } 
    }
}